package cert.dal;

import java.security.cert.X509Certificate;
import java.sql.ResultSet;
import java.util.ArrayList;

import sun.security.pkcs.PKCS10;

import cert.model.CSR;
import cert.utils.CSRUtility;
import cert.utils.CertGen;

public class Crud {

	JDBCManager jdbcmanager;

	public Crud() {
		this.jdbcmanager = new JDBCManager();
	}

	public void addCaCertificate(String name, String cert) throws Exception {
		if (jdbcmanager.connect()) {
			String query = "insert into ca (name,certificate)values('" + name
					+ "','" + cert + "')";
			jdbcmanager.execUpdate(query);
			jdbcmanager.close();
		}
	}

	public ArrayList<CSR> getCSRsByState(int state) throws Exception {
		ArrayList<CSR> liste = new ArrayList<>();
		if (jdbcmanager.connect()) {
			String query = "select id,date,state,csr from csr where state ='"
					+ state + "'";
			ResultSet rs = this.jdbcmanager.execQuery(query);
			while (rs.next()) {
				CSR csr = new CSR(rs.getInt(1), rs.getTimestamp(2),
						rs.getInt(3), CSRUtility.ReadCSR(rs.getString(4)));
				liste.add(csr);
			}
			jdbcmanager.close();
		}
		return liste;
	}

	public void updateCSRState(int state, int id) throws Exception {
		if (jdbcmanager.connect()) {
			String query = "update csr set state = '" + state + "' where id ='"
					+ id + "'";
			jdbcmanager.execUpdate(query);
			jdbcmanager.close();
		}
	}

	public void addUserCertificate(int userId, int csrId, String cert)
			throws Exception {
		if (jdbcmanager.connect()) {
			String query = "insert into certificates(userId,csrId,certificate) values('"
					+ userId + "','" + csrId + "','" + cert + "')";
			jdbcmanager.execUpdate(query);
			jdbcmanager.close();
		}

	}

	public int getUserIdByCSRId(int CSRId) throws Exception {
		int id = 0;
		if (jdbcmanager.connect()) {
			String query = "SELECT u.id FROM users u join csr c on c.userId = u.id where c.id = '"
					+ CSRId + "'";
			ResultSet rs = jdbcmanager.execQuery(query);
			while (rs.next()) {
				id = rs.getInt(1);
			}
			jdbcmanager.close();
		}
		return id;

	}

	public String getCAName() throws Exception {
		String name = "";
		if (jdbcmanager.connect()) {
			String query = "select name from ca";
			ResultSet rs = jdbcmanager.execQuery(query);
			while (rs.next()) {
				name = rs.getString(1);
				break;
			}
			jdbcmanager.close();
		}
		return name;

	}

	public String getCaCertificateString() throws Exception {
		String cert = "";
		if (jdbcmanager.connect()) {
			String query = "select certificate from ca";
			ResultSet rs = jdbcmanager.execQuery(query);
			while (rs.next()) {
				cert = rs.getString(1);
				break;
			}
			jdbcmanager.close();
		}
		return cert;
	}

	public Object[][] getIssuedCertificate() throws Exception {
		if (jdbcmanager.connect()) {
			String query = "SELECT csr.id,csr.csr,u.lastName,u.firstname,u.email,cert.certificate from csr csr join certificates cert on csr.id = cert.csrId join users u on u.id = cert.userId";
			ResultSet rs = jdbcmanager.execQuery(query);
			//ResultSet rs = jdbcmanager.execQuery(query);
			
			int rows = 0;
			while (rs.next()) {
				rows++;
			}

			Object[][] data = new Object[rows][12];

			int currentRow = 0;
			while (rs.previous()) {

				data[currentRow][0] = rs.getString("id");
				data[currentRow][1] = rs.getString("firstName") + " "+ rs.getString("lastName");
				
				String certStr = rs.getString("certificate");
				X509Certificate cert = CertGen.getCertifcate(certStr);
				
				data[currentRow][2] = certStr;
				data[currentRow][3] = cert.getSerialNumber();
				data[currentRow][4] = cert.getNotBefore();
				data[currentRow][5] = cert.getNotAfter();
				
				String csrStr = rs.getString("csr");
				PKCS10 csr = CSRUtility.ReadCSR(csrStr);
				
				data[currentRow][6] = csr.getSubjectName().getCountry();
				data[currentRow][7] = csr.getSubjectName().getOrganization();
				data[currentRow][8] = csr.getSubjectName().getOrganizationalUnit();
				data[currentRow][9] = csr.getSubjectName().getLocality();
				data[currentRow][10] = csr.getSubjectName().getState();
				data[currentRow][1] = rs.getString("email");
			}
			
			jdbcmanager.close();
			return data;
		}
		return null;
	}

}
